package com.ibm.db2oncampus.portal.usuario.persistence.impl;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.FlushMode;
import org.hibernate.Query;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.springframework.dao.DataAccessException;

import com.ibm.db2oncampus.portal.framework.orm.hibernate.support.HibernateDaoSupport;
import com.ibm.db2oncampus.portal.usuario.domain.Experience;
import com.ibm.db2oncampus.portal.usuario.domain.Person;
import com.ibm.db2oncampus.portal.usuario.persistence.PersonDao;
import com.ibm.db2oncampus.portal.util.Validadores;

/**
 * 
 * @author Wellington
 *
 */
public class PersonDaoImpl extends HibernateDaoSupport<Person> implements PersonDao {

    public PersonDaoImpl() {
    }
	
    @Override
    public void save(Person entity) throws DataAccessException {
    	getSession().setFlushMode(FlushMode.AUTO);
    	if(entity.getId() == null) {
    		getSession().save(entity);
    	} else {
    		getSession().merge(entity);
    	}
    	getHibernateTemplate().flush();
    }
    
	@Override
	public void remove(Person entity) throws DataAccessException {
		entity.setActiveProfile(Boolean.FALSE);
		super.save(entity);
	}

	@Override
	public Person findPersonByUserId(String userId) {
		Criteria criteria = getSession().createCriteria(Person.class);
		criteria.add(Expression.like("userId", userId, MatchMode.EXACT));

		List<Person> peopleFound = (List<Person>) criteria.list();

		Person foundPerson = null;
		
		if(peopleFound != null && !peopleFound.isEmpty()) {
			foundPerson = (Person) criteria.list().get(0);
		}

		return foundPerson;
		
	}	

	@Override
	public List<Person> listPersonByCertification(String certificationName) {
		StringBuilder consulta = null;

		// Define o valor da consulta
		consulta = new StringBuilder();
		consulta.append("select e.person ");
		consulta.append("from Certification e ");
		consulta.append("where upper(e.skillName) like '%");
		consulta.append(certificationName.toUpperCase());
		consulta.append("%' ");
		consulta.append("order by e.person.firstName");
			
		// Cria a consulta
		Query query = getSession().createQuery(consulta.toString());
		query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		List<Person> list = (List<Person>) query.list();
		
		return list;
		
	}

	@Override
	public List<Person> listPersonByJob(String byJobName, String byJobDescription, String byJobSkill) {

		StringBuilder consulta = null;

		// Define o valor da consulta
		consulta = new StringBuilder();
		consulta.append("select e.person ");
		consulta.append("from Job e ");
		consulta.append("where upper(");
		
		if (! Validadores.isStringEmpty(byJobName)) {
			consulta.append("e.position) like '%");
			consulta.append(byJobName.toUpperCase());
			consulta.append("%' ");
			
			if ((! Validadores.isStringEmpty(byJobDescription)) || (! Validadores.isStringEmpty(byJobSkill))) {
				consulta.append("and upper(");
			}
		}
				
		if (! Validadores.isStringEmpty(byJobDescription)) {
			consulta.append("e.positionDescription) like '%");
			consulta.append(byJobDescription.toUpperCase());
			consulta.append("%' ");
			
			if (! Validadores.isStringEmpty(byJobSkill)) {
				consulta.append("and upper(");
			}			
			
		}
		
		if (! Validadores.isStringEmpty(byJobSkill)) {
			consulta.append("e.skillsUsed) like '%");
			consulta.append(byJobSkill.toUpperCase());
			consulta.append("%' ");
		}
		
		consulta.append("order by e.person.firstName");
			
		// Cria a consulta
		Query query = getSession().createQuery(consulta.toString());
		query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		List<Person> list = (List<Person>) query.list();
		
		return list;		
		
		
	}

	@Override
	public List<Person> listPersonByName(String name) {
		Criteria criteriaPerson = getSession().createCriteria(Person.class);
		criteriaPerson.add(Expression.ilike("firstName", name, MatchMode.ANYWHERE));
		criteriaPerson.addOrder(Order.asc("firstName"));
		List<Person> list = (List<Person>) criteriaPerson.list();
		return list;
	}

	@Override
	public List<Person> listPersonByOtherSkill(String otherSkillName) {
		StringBuilder consulta = null;

		// Define o valor da consulta
		consulta = new StringBuilder();
		consulta.append("select e.person ");
		consulta.append("from OtherSkill e ");
		consulta.append("where upper(e.description) like '%");
		consulta.append(otherSkillName.toUpperCase());
		consulta.append("%' ");
		consulta.append("order by e.person.firstName");
			
		// Cria a consulta
		Query query = getSession().createQuery(consulta.toString());
		query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		List<Person> list = (List<Person>) query.list();
		
		return list;
	}

	@Override
	public List<Person> listPersonByScholarship(String courseName, String level) {
		StringBuilder consulta = null;

		// Define o valor da consulta
		consulta = new StringBuilder();
		consulta.append("select e.person ");
		consulta.append("from Graduation e ");
		consulta.append("where upper(");
		
		if (! Validadores.isStringEmpty(courseName)) {
			consulta.append("e.courseName) like '%");
			consulta.append(courseName.toUpperCase());
			consulta.append("%' ");
			
			if (! Validadores.isStringEmpty(level)) {
				consulta.append("and upper(");
			}
		}
				
		if (! Validadores.isStringEmpty(level)) {
			consulta.append("e.graduationLevel) like '%");
			consulta.append(level.toUpperCase());
			consulta.append("%' ");
		}
		
		consulta.append("order by e.person.firstName");
			
		// Cria a consulta
		Query query = getSession().createQuery(consulta.toString());
		query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
		List<Person> list = (List<Person>) query.list();
		
		return list;
	}

	@Override
	public Experience findExperienceById(Long experienceId) {
		return (Experience) getHibernateTemplate().get(Experience.class, experienceId);
	}
	
	public List<Experience> listExperienceNotInCollectionByPerson(Person person, List<Experience> experienceList) {
		
		Long[] values = new Long[experienceList.size()];
		for (int i = 0; i < experienceList.size(); i++) {
			values[i] = experienceList.get(i).getExperienceId();
		}
		
		Criteria criteriaExperience = getSession().createCriteria(Experience.class);
		criteriaExperience.add(Expression.eq("person.id", person.getId()));
		criteriaExperience.add(Expression.not(Expression.in("id", values)));
		return criteriaExperience.list();
	}
	
	public void removeExperience(Experience experience) {
		getSession().setFlushMode(FlushMode.AUTO);
		getSession().delete(experience);
		getSession().flush();
	}
}